﻿using System;
using System.Data.OleDb;
using System.IO;
using System.Linq.Expressions;
using SBACN.WebSite.Code;
using SBACN.WebSite.Models;
using System.Web;

namespace SBACN.WebSite.Education
{
	public partial class MemberExamScoreDown : MemberBasePage
	{
		protected int MID
		{
			get
			{
				return  MemberBasePage.UserInfo.MemberID;
			}
		}

		protected void Page_Load(object sender, EventArgs e)
		{
			if (!Page.IsPostBack)
			{
				int ID;
				if (int.TryParse(Request.QueryString["id"], out ID))
					ExportToExcel(ID);
			}
		}

		public void ExportToExcel(int ID)
		{
			Expression<Func<View_ExamScore, bool>> f;
			if (this.MID == -1)
			{
				f = (c => c.ID == ID && c.Status == 0);
			}
			else
			{
				f = (c => c.ID == ID && c.MemberID == this.MID && c.Status == 0);
			}

			Expression<Func<View_ExamScore, int>> order = (c => c.ID);
			var resultE = DAL.DALHelper.Instance.GetList<View_ExamScore, int>(f, order, true);

			bool isSimple = (resultE.Count == 0 || !string.IsNullOrWhiteSpace(resultE[0].Score));

			string strTmpFileName = isSimple ? "~/Template/考试成绩表_S.xls" : "~/Template/考试成绩表_F.xls";
			string strDownloadFileName = "~/Template/Down/考试成绩表"+ DateTime.Now.Ticks +".xls";

			File.Copy(Server.MapPath(strTmpFileName), Server.MapPath(strDownloadFileName), true);

			string strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 8.0;HDR=Yes'";

			using (OleDbConnection conn = new OleDbConnection(strExcelConn))
			{
				conn.Open();
				OleDbCommand excmd = new OleDbCommand();//insertcmd, conn);
				foreach (var l in resultE)
				{
					string insertcmd = isSimple ? "INSERT INTO [Sheet1$] (科目,会员单位,姓名,身份证号,成绩) VALUES ('" + l.Subject + "','" + l.MemberName + "','" + l.Name + "','" + l.IDCardNO + "','" + l.Score + "') " : "INSERT INTO [Sheet1$] (科目,会员单位,姓名,身份证号,业务成绩,政策成绩) VALUES ('" + l.Subject + "','" + l.MemberName + "','" + l.Name + "','" + l.IDCardNO + "','" + l.ScoreProfession + "','" + l.ScorePolicy + "') ";
					excmd.CommandText = insertcmd;
					excmd.Connection = conn;
					excmd.ExecuteNonQuery();
				}
				conn.Close();
			}
			Response.Redirect(strDownloadFileName);
		}
	}
}